﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using FreeOnlineApplication.Class.Entity;
using MySql.Data.MySqlClient;
using System.Configuration;
using System.Collections;

namespace FreeOnlineApplication.Class.Repository
{
    public class BookingRepository
    {
        protected MySqlConnection m_connection;
        MySqlCommand aCommand = new MySqlCommand();

        public BookingRepository(MySqlConnection connection)
        {
            m_connection = connection;
            aCommand.Connection = m_connection;
        }
        public void Insert(Booking booking)
        {
            string insertSQL = String.Format(@"insert into table_booking 
                (
                    booking_code,
                    booking_date,
                    customer_id,
                    roommaster_id,
                    booking_disc,
                    booking_start,
                    booking_end,
                    booking_status,
                    booking_note,
                    user_id,
                    unit_id) 
                VALUES ('{0}','{1}',{2},{3},'{4}','{5}','{6}','{7}','{8}','{9}','{10}')",
                booking.CODE, booking.DATE.ToString(Tools.DATE_FORMAT_SHORT), booking.CUSTOMER.ID, booking.ROOM_MASTER.ID,
                booking.DISCOUNT, booking.START_DATE_TIME.ToString(Tools.DATE_FORMAT),
                booking.END_DATE_TIME.ToString(Tools.DATE_FORMAT),booking.STATUS,
                booking.NOTE,booking.USER.ID, booking.UNIT.ID);
            aCommand.CommandText = insertSQL;
            aCommand.ExecuteNonQuery();
            booking.ID = GetMaximumId();
        }
        public Booking GetById(int id)
        {
            string getByID = String.Format("select * from table_booking where booking_id = {0}", id);
            aCommand.CommandText = getByID;
            MySqlDataReader aReader = aCommand.ExecuteReader();
            Booking booking = transform(aReader);
            aReader.Close();
            return booking;
        }
        public void Update(Booking booking)
        {
            string updateSQL = String.Format(@"update table_booking set 
                    booking_code = '{0}',
                    customer_id = '{1}',
                    roommaster_id = '{2}',
                    booking_disc = '{3}',
                    booking_start = '{4}',
                    booking_end = '{5}',
                    booking_status = '{6}',
                    booking_note = '{7}',
                    user_id = {8},
                    unit_id = {9},
                    booking_date = '{10}'
                where booking_id = {11}",
                booking.CODE, booking.CUSTOMER.ID, booking.ROOM_MASTER.ID,
                booking.DISCOUNT, booking.START_DATE_TIME.ToString(Tools.DATE_FORMAT), 
                booking.END_DATE_TIME.ToString(Tools.DATE_FORMAT),
                booking.STATUS, booking.NOTE,booking.USER.ID, booking.UNIT.ID, 
                booking.DATE.ToString(Tools.DATE_FORMAT_SHORT), booking.ID);
            aCommand.CommandText = updateSQL;
            aCommand.ExecuteNonQuery();
        }
        public void Delete(Booking booking)
        {
            string deleteSQL = "delete from table_booking where booking_id = " + booking.ID;
            aCommand.CommandText = deleteSQL;
            aCommand.ExecuteNonQuery();
        }
        public int GetMaximumId()
        {
            string getByID = String.Format("SELECT max(booking_id) from table_booking");
            aCommand.CommandText = getByID;
            object r = aCommand.ExecuteScalar();
            int aReader = r is System.DBNull ? 0 : Convert.ToInt32(r);
            return aReader;
        }
        public int GetCount(int unit)
        {
            string getByID = String.Format("SELECT count(*) from table_booking where unit_id = {0}",unit);
            aCommand.CommandText = getByID;
            object r = aCommand.ExecuteScalar();
            int aReader = r is System.DBNull ? 0 : Convert.ToInt32(r);
            return aReader;
        }
        public IList GetAll(int unit)
        {
            string sql = String.Format("select * from table_booking where unit_id = {0}", unit);
            aCommand.CommandText = sql;
            MySqlDataReader aReader = aCommand.ExecuteReader();
            IList users = transformList(aReader);
            aReader.Close();
            return users;
        }
        public IList GetAllOpen(int unit)
        {
            string sql = String.Format("select * from table_booking where booking_status = 'OPEN' and unit_id = {0}", unit);
            aCommand.CommandText = sql;
            MySqlDataReader aReader = aCommand.ExecuteReader();
            IList users = transformList(aReader);
            aReader.Close();
            return users;
        }
        public void UpdateToClose(int booking)
        {
            string sql = String.Format(@"update table_booking set booking_status = 'CLOSE' 
            where booking_id = {0}", booking);
            aCommand.CommandText = sql;
            aCommand.ExecuteNonQuery();
        }
        public Booking FindBookingByRoomMasterAndDate(RoomMaster rm, DateTime date)
        {
            string sql = String.Format(@"SELECT * FROM table_booking t
                            where t.roommaster_id = {0}
                            and '{1}' between t.booking_start and t.booking_end
                            and t.booking_status = 'OPEN'", rm.ID, date.ToString(Tools.DATE_FORMAT));
            aCommand.CommandText = sql;
            MySqlDataReader aReader = aCommand.ExecuteReader();
            Booking users = transform(aReader);
            aReader.Close();
            return users;
        }
        public IList GetAllSearchOpen(string find, int unit)
        {
            string sql = String.Format(@"select t.* from table_booking t, table_customer c where 
            concat(c.customer_code, c.customer_idno, c.customer_name,
                c.customer_address, c.customer_email, c.customer_phone, c.customer_job,
                c.customer_gender, t.booking_code, t.booking_date,
                t.booking_code, 
                DATE_FORMAT(t.booking_start, '%d-%m-%Y %H:%i:%s'),
                DATE_FORMAT(t.booking_end, '%d-%m-%Y %H:%i:%s')
                ) like '%{0}%' and t.unit_id = {1} and  t.booking_status = 'OPEN'
                and t.customer_id = c.customer_id", find, unit);
            aCommand.CommandText = sql;
            MySqlDataReader aReader = aCommand.ExecuteReader();
            IList users = transformList(aReader);
            aReader.Close();
            return users;
        }
        public IList GetAllSearch(string find, int unit)
        {
            string sql = String.Format(@"select t.* from table_booking t, table_customer c where 
            concat(c.customer_code, c.customer_idno, c.customer_name,
                c.customer_address, c.customer_email, c.customer_phone, c.customer_job,
                c.customer_gender, t.booking_code, 
                DATE_FORMAT(t.booking_date, '%d-%m-%Y %H:%i:%s'),
                DATE_FORMAT(t.booking_start, '%d-%m-%Y %H:%i:%s'),
                DATE_FORMAT(t.booking_end, '%d-%m-%Y %H:%i:%s'),t.booking_status
                ) like '%{0}%' and t.unit_id = {1}
                and t.customer_id = c.customer_id", find, unit);
            aCommand.CommandText = sql;
            MySqlDataReader aReader = aCommand.ExecuteReader();
            IList users = transformList(aReader);
            aReader.Close();
            return users;
        }
        private Booking transform(MySqlDataReader aReader)
        {
            Booking booking = null;
            while (aReader.Read())
            {
                booking = new Booking();
                booking.ID = Convert.ToInt32(aReader["booking_id"]);
                booking.CODE = aReader["booking_code"].ToString();
                booking.CUSTOMER = new Customer(Convert.ToInt32(aReader["customer_id"]));
                booking.ROOM_MASTER = new RoomMaster(Convert.ToInt32(aReader["roommaster_id"]));
                booking.DISCOUNT = Convert.ToDouble(aReader["booking_disc"]);
                booking.START_DATE_TIME = Convert.ToDateTime(aReader["booking_start"]);
                booking.END_DATE_TIME = Convert.ToDateTime(aReader["booking_end"]);
                booking.STATUS = aReader["booking_status"].ToString();
                booking.NOTE = aReader["booking_note"].ToString();
                booking.UNIT = new Unit(Convert.ToInt32(aReader["unit_id"]));
                booking.USER = new User(Convert.ToInt32(aReader["user_id"]));
                booking.DATE = Convert.ToDateTime(aReader["booking_date"]);

            }
            return booking;
        }
        private IList transformList(MySqlDataReader aReader)
        {
            IList result = new ArrayList();
            while (aReader.Read())
            {
                Booking booking = new Booking();
                booking.ID = Convert.ToInt32(aReader["booking_id"]);
                booking.CODE = aReader["booking_code"].ToString();
                booking.CUSTOMER = new Customer(Convert.ToInt32(aReader["customer_id"]));
                booking.ROOM_MASTER = new RoomMaster(Convert.ToInt32(aReader["roommaster_id"]));
                booking.DISCOUNT = Convert.ToDouble(aReader["booking_disc"]);
                booking.START_DATE_TIME = Convert.ToDateTime(aReader["booking_start"]);
                booking.END_DATE_TIME = Convert.ToDateTime(aReader["booking_end"]);
                booking.STATUS = aReader["booking_status"].ToString();
                booking.NOTE = aReader["booking_note"].ToString();
                booking.UNIT = new Unit(Convert.ToInt32(aReader["unit_id"]));
                booking.USER = new User(Convert.ToInt32(aReader["user_id"]));
                booking.DATE = Convert.ToDateTime(aReader["booking_date"]);

                result.Add(booking);
            }
            return result;
        }
    }
}
